﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;

namespace MySqlTest2
{
    internal class DatabaseManager
    {
        private MySqlConnection conn = null;
        private string connectionString = "Server=localhost;Database=testdb;Uid=root;Pwd=sww1433223";
        //数据库连接
        public bool OpenConnection()
        {
            try
            {
                if (conn == null)
                {
                    conn = new MySqlConnection(connectionString);               
                }
                if(conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                    Console.WriteLine("数据库连接成功！");
                    return true;
                }
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"数据库连接失败：{ex.Message}");
                return false;
            }
        }
        //关闭数据库连接
        public void CloseConnection()
        {
            if(conn!=null && conn.State != System.Data.ConnectionState.Closed)
            {
                conn.Close();
                Console.WriteLine("数据库连接已关闭！");
            }
        }

        //查询
        public void SelectData()
        {
            if (!OpenConnection())
            {
                return;
            }

            string sql = "select id,name,age,sal from users";

            try
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    using(MySqlDataReader reader= cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine("查询结果：");
                            while (reader.Read())
                            {
                                int id = reader.GetInt32("id");
                                string name = reader.GetString("name");
                                int age = reader.GetInt32("age");
                                decimal salary = reader.GetDecimal("sal");
                                Console.WriteLine($"ID: {id}, 姓名：{name},  年龄: {age},  工资：{salary}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("查询结果为空！");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询数据错误: {ex.Message}");
            }
            finally
            {
                CloseConnection();
            }
        }

        //插入
        public void InsertData(int id,string name,int age,decimal sal,string password)
        {
            if (!OpenConnection())
                return;

            string sql = "insert into users (id,name,age,sal,password) values (@id,@name,@age,@sal,@password)";
            try
            {
                using(MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@age", age);
                    cmd.Parameters.AddWithValue("@sal", sal);
                    cmd.Parameters.AddWithValue("@password", password);

                    int rows = cmd.ExecuteNonQuery();
                    Console.WriteLine($"{rows}行数据插入成功！");
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine($"插入数据错误：{ex.Message}");
            }
            finally
            {
                CloseConnection();
            }
        }

        //更新
        public void UpdateData(int id, string name, int age, decimal salary, string password)
        {
            // 打开连接，失败则返回
            if (!OpenConnection()) return;

            // 定义更新SQL语句，使用参数化查询，通过WHERE子句指定要更新的记录（根据id）
            string sql = "UPDATE users SET name = @name, age = @age, sal = @salary ,password=@password WHERE id = @id";

            try
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    // 为所有参数赋值（包括WHERE条件中的id参数）
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@age", age);
                    cmd.Parameters.AddWithValue("@salary", salary);
                    cmd.Parameters.AddWithValue("@password", password);

                    // 执行更新操作，返回受影响的行数（若id不存在，返回0）
                    int rows = cmd.ExecuteNonQuery();
                    Console.WriteLine($"{rows} 行数据更新成功");
                }
            }
            catch (Exception ex)
            {
                // 捕获更新过程中的异常（如id不存在、数据格式错误等）
                Console.WriteLine($"更新数据错误: {ex.Message}");
            }
            finally
            {
                // 关闭连接
                CloseConnection();
            }
        }

        //删除
        public void DeleteData(int id)
        {
            if (!OpenConnection())
                return;
            string sql = "delete from users where id = @id"; 
            try
            {
                using(MySqlCommand cmd=new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    int rows = cmd.ExecuteNonQuery();
                    Console.WriteLine($"{rows}行数据已被删除！");
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine($"删除异常：{ex.Message}");
            }
            finally
            {
                CloseConnection();
            }
        }
          
    }
}
